package com.example.bluetoothclicker.db

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.content.ContentValues

class SQLiteHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        private const val DATABASE_NAME = "commands.db"
        private const val DATABASE_VERSION = 1

        // 表和列定义
        const val TABLE_COMMANDS = "commands"
        const val COLUMN_BUTTON_NAME = "button_name"
        const val COLUMN_COMMAND = "command"

        // 创建表的 SQL
        private const val CREATE_TABLE_COMMANDS = """
            CREATE TABLE $TABLE_COMMANDS (
                $COLUMN_BUTTON_NAME TEXT PRIMARY KEY,
                $COLUMN_COMMAND TEXT
            )
        """
    }

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(CREATE_TABLE_COMMANDS) // 创建表
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS $TABLE_COMMANDS") // 如果表存在，删除旧表
        onCreate(db) // 重新创建表
    }

    /**
     * 插入或更新按钮的命令
     */
    fun insertOrUpdateCommand(buttonName: String, command: String): Boolean {
        val db = writableDatabase
        val values = ContentValues().apply {
            put(COLUMN_BUTTON_NAME, buttonName)
            put(COLUMN_COMMAND, command)
        }
        val result = db.insertWithOnConflict(
            TABLE_COMMANDS, null, values, SQLiteDatabase.CONFLICT_REPLACE
        )
        db.close()
        return result != -1L
    }

    /**
     * 根据按钮名称获取命令
     */
    fun getCommand(buttonName: String): String? {
        val db = readableDatabase
        val cursor = db.query(
            TABLE_COMMANDS,
            arrayOf(COLUMN_COMMAND),
            "$COLUMN_BUTTON_NAME = ?",
            arrayOf(buttonName),
            null,
            null,
            null
        )
        val command = if (cursor.moveToFirst()) {
            cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_COMMAND))
        } else {
            null
        }
        cursor.close()
        db.close()
        return command
    }

    /**
     * 获取所有命令
     */
    fun getAllCommands(): Map<String, String> {
        val commands = mutableMapOf<String, String>()
        val db = readableDatabase
        val cursor = db.query(
            TABLE_COMMANDS,
            null,
            null,
            null,
            null,
            null,
            null
        )
        while (cursor.moveToNext()) {
            val buttonName = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_BUTTON_NAME))
            val command = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_COMMAND))
            commands[buttonName] = command
        }
        cursor.close()
        db.close()
        return commands
    }
}
